﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAO;
using System.Data.SqlClient;
using System.Data;
using APA_DAO.Entities;
namespace APA_DAO
{
    public class ThuePhongDao
    {
        public ThuePhongDao()
        {
            new SqlHelper();
        }

        public bool huyThuTien(int id)
        {
            try
            {
                SqlTransaction sqlTransaction;
                SqlConnection con = new SqlConnection(SqlHelper.ConnectString);
                con.Open();
                sqlTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    if (!SqlHelper.ExecuteNonQueryWithTranSaction("SP_DELETE_THU_TIEN", sqlTransaction, con,
                        "@ID", id,
                        "@USR_ID", LoginDao.userID))
                    {
                        sqlTransaction.Rollback();
                        return false;
                    }

                    sqlTransaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    sqlTransaction.Rollback();
                    Console.WriteLine(e);
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
                return false;
            }
        }

        public bool huyThuePhong(int soThuePhong)
        {
            try
            {
                SqlTransaction sqlTransaction;
                SqlConnection con = new SqlConnection(SqlHelper.ConnectString);
                con.Open();
                sqlTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    if (!SqlHelper.ExecuteNonQueryWithTranSaction("SP_HUY_THUE_PHONG", sqlTransaction, con,
                        "@ID", soThuePhong,
                        "@USR_ID", LoginDao.userID))
                    {
                        sqlTransaction.Rollback();
                        return false;
                    }

                    sqlTransaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    sqlTransaction.Rollback();
                    Console.WriteLine(e);
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
                return false;
            }
        }


        public bool updateThuePhong(int soThuePhong, int maPhong, float tienDacCoc, float donGia, float chiSoNuoc, 
            float chiSoDien, DateTime ngayVaoO, int soThang, int flg)
        {
            try
            {
                SqlTransaction sqlTransaction;
                SqlConnection con = new SqlConnection(SqlHelper.ConnectString);
                con.Open();
                sqlTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    if (!SqlHelper.ExecuteNonQueryWithTranSaction("SP_UPDATE_THUE_PHONG", sqlTransaction, con,
                       "@SO_THUE_PHONG", soThuePhong,
                       "@MA_PHONG", maPhong,
                       "@TIEN_COC", tienDacCoc,
                       "@DON_GIA", donGia,
                       "@SO_THANG", soThang,
                       "@CHI_SO_NUOC", chiSoNuoc,
                       "@CHI_CHO_DIEN", chiSoDien,
                       "@NGAY_VAO_O", ngayVaoO,
                       "@FLG", flg,
                       "@USR_ID", LoginDao.userID))
                    {
                        sqlTransaction.Rollback();
                        return false;
                    }
                    sqlTransaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    sqlTransaction.Rollback();
                    Console.WriteLine(e);
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
                return false;
            }
        }

        public DataRow GetThuePhonGInfor4Edit(int id)
        {
            DataTable table = SqlHelper.ExecuteDataTable("SP_GET_INFOR_OF_THUE_PHONG_4_EDIT", CommandType.StoredProcedure,
                "@ID", id);
            if (table != null && table.Rows.Count > 0)
            {
                return table.Rows[0];
            }
            return null;
        }

        public DataTable getRoomList4Edit(int id)
        {
            return SqlHelper.ExecuteDataTable("SP_GET_PHONG_FOR_EDIT", CommandType.StoredProcedure,
                "@SO_PHONG", id);
        }

        public DataTable getCustomerHist(int id)
        {
            return SqlHelper.ExecuteDataTable("SP_GET_CUS_HIST", CommandType.StoredProcedure,
                "@ID", id);
        }

        public DataTable getThuePhongs(string str)
        {
            return SqlHelper.ExecuteDataTable("SP_GET_THUE_PHONG_LIST", CommandType.StoredProcedure,
                "@SEARCH_STRING", str);
        }

        public DataRow getThuePhong(int id)
        {
            DataTable table = SqlHelper.ExecuteDataTable("SP_GET_INFOR_BY_THUE_PHONG", CommandType.StoredProcedure,
                "@ID", id);
            if (table != null)
            {
                return table.Rows[0];
            }
            return null;
        }

        public DataRow getPrice(int id)
        {
            DataTable table = SqlHelper.ExecuteDataTable("SP_GET_PRICE_AND_COST", CommandType.StoredProcedure,
                "@ID", id);
            return table == null ? null : table.Rows[0];
        }

        public bool print(int soThuePhong, DateTime ngayThu, float chiSoNuocTruoc, float chiSoNuocSau,
            float chiSoDienTruoc, float chiSoDienSau, float tienPhong, float SoThang, float chiPhKhac, DateTime ngayKetThuc,
            float donGiaDien, float donGiaNuoc, float motoFee)
        {
            return SqlHelper.ExecuteNonQuery("SP_PRINT_BEFORE", CommandType.StoredProcedure,
                "@SO_THUE_PHONG", soThuePhong,
                        "@NGAY_THU", ngayThu,
                        "@NGUOI_THU", LoginDao.userID,
                        "@CHI_SO_NUOC_TRUOC", chiSoNuocTruoc,
                        "@CHI_SO_NUOC_SAU", chiSoNuocSau,
                        "@CHI_SO_DIEN_TRUOC", chiSoDienTruoc,
                        "@CHI_SO_DIEN_SAU", chiSoDienSau,
                        "@DON_GIA_DIEN", donGiaDien,
                        "@DON_GIA_NUOC", donGiaNuoc,
                        "@TIEN_PHONG", tienPhong,
                        "@TIEN_GUI_XE", motoFee,
                        "@SO_THANG", SoThang,
                        "@TIEN_PHU_THU", chiPhKhac,
                        "@NGAY_KT_KY_TRUOC", ngayKetThuc,
                        "@NGAY_KET_THUC_KY_NAY", ngayKetThuc.AddMonths((int)SoThang));
        }

        public DataRow getPrint(int id)
        {
            DataTable table = SqlHelper.ExecuteDataTable("SP_GET_PRINT", CommandType.StoredProcedure,
                "@ID", id);
            if (table != null && table.Rows.Count > 0)
            {
                return table.Rows[0];
            }
            return null;
        }

        public bool traPhong(int soThuePhong, DateTime ngayThu, float chiSoNuocTruoc, float chiSoNuocSau,
            float donGiaNuoc, float chiSoDienTruoc, float chiSoDienSau, float donGiaDien, float chiPhiKhac)
        {
            try
            {
                SqlTransaction sqlTransaction;
                SqlConnection con = new SqlConnection(SqlHelper.ConnectString);
                con.Open();
                sqlTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    if (!SqlHelper.ExecuteNonQueryWithTranSaction("SP_TRA_PHONG", sqlTransaction, con,
                        "@SO_THUE_PHONG", soThuePhong,
                        "@NGAY_TRA", ngayThu,
                        "@CHI_SO_NUOC_TRUOC", chiSoNuocTruoc,
                        "@CHI_SO_NUOC_SAU", chiSoNuocSau,
                        "@DON_GIA_NUOC", donGiaNuoc,
                        "@CHI_SO_DIEN_TRUOC", chiSoDienTruoc,
                        "@CHI_SO_DIEN_SAU", chiSoDienSau,
                        "@DON_GIA_DIEN", donGiaDien,
                        "@CHI_PHI_KHAC", chiPhiKhac,
                        "@USR_ID", LoginDao.userID))
                    {
                        sqlTransaction.Rollback();
                        return false;
                    }
                    sqlTransaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    sqlTransaction.Rollback();
                    Console.WriteLine(e);
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
                return false;
            }
        }
        public bool thueTien(int soThuePhong, DateTime ngayThu, float chiSoNuocTruoc, float chiSoNuocSau,
            float chiSoDienTruoc, float chiSoDienSau, float tienPhong, float SoThang, float chiPhKhac, DateTime ngayKetThuc,
            float donGiaDien, float donGiaNuoc, float motoFee)
        {
            try
            {
                SqlTransaction sqlTransaction;
                SqlConnection con = new SqlConnection(SqlHelper.ConnectString);
                con.Open();
                sqlTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    if (!SqlHelper.ExecuteNonQueryWithTranSaction("SP_INSERT_THU_TIEN", sqlTransaction, con,
                        "@SO_THUE_PHONG", soThuePhong,
                        "@NGAY_THU", ngayThu,
                        "@NGUOI_THU", LoginDao.userID,
                        "@CHI_SO_NUOC_TRUOC", chiSoNuocTruoc,
                        "@CHI_SO_NUOC_SAU", chiSoNuocSau,
                        "@CHI_SO_DIEN_TRUOC", chiSoDienTruoc,
                        "@CHI_SO_DIEN_SAU", chiSoDienSau,
                        "@DON_GIA_DIEN", donGiaDien,
                        "@DON_GIA_NUOC", donGiaNuoc,
                        "@TIEN_PHONG", tienPhong,
                        "@TIEN_GUI_XE", motoFee,
                        "@SO_THANG", SoThang,
                        "@TIEN_PHU_THU", chiPhKhac,
                        "@NGAY_KT_KY_TRUOC", ngayKetThuc,
                        "@NGAY_KET_THUC_KY_NAY", ngayKetThuc.AddMonths((int)SoThang)))
                    {
                        sqlTransaction.Rollback();
                        return false;
                    }
                    sqlTransaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    sqlTransaction.Rollback();
                    Console.WriteLine(e);
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
                return false;
            }
        }

        public bool ThuePhong(int maPhong, float donGiaPhong,
            DateTime ngayThue, DateTime ngayVaoO, float ChiSoNuoc, float chiSoDien, float tienCoc,
            float tienNha, int SoThang, List<Roomer> roomers, List<Moto> motos)
        {
            try
            {
                SqlTransaction sqlTransaction;
                SqlConnection con = new SqlConnection(SqlHelper.ConnectString);
                con.Open();
                sqlTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    int soThuePhong = 0;
                    int.TryParse(SqlHelper.getStringWithTran("SP_INSERT_THUE_PHONG", sqlTransaction, con,
                        "@MA_PHONG", maPhong,
                        "@NGAY_THUE", ngayThue,
                        "@TIEN_COC", tienCoc,
                        "@DON_GIA_PHONG", donGiaPhong,
                        "@USER_ID", LoginDao.userID), out soThuePhong);
                    if (soThuePhong > 0)
                    {
                        if (!SqlHelper.ExecuteNonQueryWithTranSaction("SP_INSERT_THU_TIEN", sqlTransaction, con,
                            "@SO_THUE_PHONG", soThuePhong,
                            "@NGAY_THU", DateTime.Now,
                            "@NGUOI_THU", LoginDao.userID,
                            "@NGAY_KT_KY_TRUOC", ngayVaoO,
                            "@NGAY_KET_THUC_KY_NAY", ngayVaoO.AddMonths(SoThang),
                            "@CHI_SO_NUOC_TRUOC", null,
                            "@CHI_SO_NUOC_SAU", ChiSoNuoc,
                            "@CHI_SO_DIEN_TRUOC", null,
                            "@CHI_SO_DIEN_SAU", chiSoDien,
                            "@DON_GIA_DIEN", null,
                            "@DON_GIA_NUOC", null,
                            "@TIEN_PHONG", tienNha,
                            "@TIEN_GUI_XE", null,
                            "@TIEN_PHU_THU", null,
                            "@SO_THANG", SoThang))
                        {
                            sqlTransaction.Rollback();
                            return false;
                        }
                        int id = 0;
                        if (roomers != null)
                        {
                            foreach (Roomer roomer in roomers)
                            {
                                id = 0;
                                int.TryParse(SqlHelper.getStringWithTran("SP_INSERT_KHACH_THUE", sqlTransaction, con,
                                    "@HO_VA_TEN", roomer.TenKH,
                                    "@NGAY_SINH", roomer.BirthDay,
                                    "@GIOI_TINH", roomer.GioiTinh,
                                    "@CMND", roomer.SoCM,
                                    "@NGAY_CAP", roomer.NgayCap,
                                    "@NOI_CAP", roomer.NoiCap,
                                    "@DIA_CHI", roomer.DiaChi,
                                    "@SO_DT", roomer.SoDt,
                                    "@SO_THUE_PHONG", soThuePhong,
                                    "@HINH_ANH", roomer.PathHinhAnh,
                                    "@LEVEL", roomer.Level,
                                    "@USR_ID", LoginDao.userID), out id);
                                if (id <= 0)
                                {
                                    sqlTransaction.Rollback();
                                    return false;
                                }
                                else
                                {
                                    if (roomer.Image != null)
                                    {
                                        try
                                        {
                                            roomer.Image.Save(roomer.PathHinhAnh + id + ".JPG");
                                        }
                                        catch (Exception ex)
                                        {
                                            Console.Write(ex.ToString());
                                            sqlTransaction.Rollback();
                                            return false;
                                        }
                                    }
                                }
                            }
                        }
                        if (motos != null)
                        {
                            foreach (Moto moto in motos)
                            {
                                if (!SqlHelper.ExecuteNonQueryWithTranSaction("SP_INSERT_MOTO", sqlTransaction, con,
                                    "@SO_XE", moto.SoXe,
                                    "@SO_KHUNG", moto.SoKhung,
                                    "@SO_MAY", moto.SoMay,
                                    "@TEN_CHU_SO_HUU", moto.TenChuSoHuu,
                                    "@SO_THUE_PHONG", soThuePhong,
                                    "@USR_ID", LoginDao.userID))
                                {
                                    sqlTransaction.Rollback();
                                    return false;
                                }
                            }
                        }
                    }
                    else
                    {
                        sqlTransaction.Rollback();
                        return false;
                    }
                    sqlTransaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    sqlTransaction.Rollback();
                    Console.WriteLine(e);
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }
    }
}
